Otázka č. 19 - Datové typy, DDL, DML, omezení, sekvence a indexy

Datové typy

Datový typ označuje druh nebo význam hodnot, které reprezentuje. Určuje například jejich velikost v paměti nebo omezuje, které operace s hodnotami daného typu lze dělat. V databázových systémech nalezneme spoustu datových typů rozřazených do základních kategorií.

Číselné

Celočíselné

Název	Velikost	Rozsah
tinyint	1B	-128 až 127 nebo 0 až 255
smallint	2B	−32768 až 32 767 nebo 0 až 65 535
mediumint	3B	−8 388 608 až 8 388 607 (8 milionů) nebo 0 až 16 777 215 (16 milionů)
int	4B	−2 147 483 648 až 2 147 483 647 (2 miliardy) nebo 0 až 4 294 967 295 (4 miliardy)
bigint	8B	-2^63 až 2^63 nebo 0 až 18 446 744 073 709 551 615
bit/boolean	1b	Pravda/nepravda

Desetinné s pevnou čárkou

Název	Velikost	Parametr
Decimal (m,n)	-	m = celková velikost, n = počet míst vpravo od čárky

Desetinné s plovoucí čárkou

Název	Velikost
float	4B
double	8B
real	4B

Řetězcové (textové)

Název	Parametr	Popis
char(m)	m = délka řetězce 0-255	Pokud se vložený řetězec kratší, doplní se volná mísa mezerami
varchar(m)	m = délka řetězce 0-255	Pokud se vložený řetězec kratší, volná místa se nedoplňují
tinytext		Max 255 znaků
text		Max 65 535 znaků
mediumtext		Maximum je přes 16 milionů znaků
longtext		Maximum je přes 4 miliardy znaků
binary (m)	m = délkařetězce	Pokud se vložený řetězec kratší, doplní se volná mísa mezerami
varbinary(m)	m = délka řetězce	Pokud se vložený řetězec kratší, volná místa se nedoplňují
tinyblob		Max 255 znaků
blob		Max 65 535 znaků
mediumblob		Maximum je přes 16 milionů znaků
longblob		Maximum je přes 4 miliardy znaků
enum(items)	max 65 535 řetězců	Pole předem definovaných řetězců, výběr jednoho z nabídky
set(items)	max 64 řetězců	Pole předem definovaných řetězců, výběr více z nabídky

*Datové typy blob se používají především k ukládání obrázků v textové podobě

*Datové typy Binary slouží pro ukládání hashů, kontr. součů a dalších binárních dat

Datumové

Název	Popis
Date	Ukládání datumu rrrr-mm-dd
Time	Ukládání času hh:mm:ss
Datetime	Zřetězení datumu a času rrrr-mm-dd jj:mm:ss
Timestamp	Podobný jako datetime a interně je to hodnota počtu sekund od 1.1.1970
Year	Pro ukládání čísla ve formátu yyyy

Speciální

Název	Popis
geometry	Jakýkoliv typ geometrické hodnoty
point	Ukládání souřadnic x a y
polygon	Ukládání bodů, určujících křivku
linestring	Ukládání bodů určující tvar
multipoint	Ukládání více jednotlivých bodů
multilinestring	Ukládání více křivek
multipolygon	Ukládání více tvarů

DDL a DML

Příkazy jazyka SQL se řadí do kategorií podle toho, jak daný příkaz s daty manipuluje. Dvěma z těchto kategorií jsou právě DDL a DML.

• DDL = „data definition language“
  • Příkazy DDL vytvářejí schéma databáze, její strukturu a pravidla. Upravuje také veškeré objekty databáze (tabulky, indexy, pohledy a omezení). Nepřistupují přímo k datům. Jedná se o příkazy:

    CREATE

    Příkaz vytvářející objekty databáze – tabulky, indexy, funkce, pohledy, procedury, triggery a další…

    Create [type] [name](parametry)
    
    -- příklad
    CREATE TABLE table (
          id INT,
            name VARCHAR(255)
      );

    DROP

    Příkaz používající se k odstranění objektů z databáze, případně i k odstranění databáze celé.

    DROP [type] [name]
    
    -- příklad
    
    DROP TABLE table;
    DROP DATABASE database; 

    ALTER

    Příkaz, který se používá k úpravě objektů v databázi. Mohou to být úpravy jako přidávání sloupců, atd.. K tomuto příkazu se ještě dále používají dodatečné příkazy ADD, DROP, MODIFY.

    ALTER [type] [name] [ADD/DROP/MODIFY] [parametry]
    
    -- příklad
    
    ALTER TABLE table ADD age INT;
    

    TRUNCATE

    I když na začátku bylo řečeno že DDl nepřistupuje přímo k datům, tento příkaz je mírnou výjimkou. Slouží totiž k vyprázdnění tabulky (tvz. Vymaže všechny data, které se v ní nachází).

    TRUNCATE TABLE [name];

    COMMENT

    Příkaz sloužící k přidávání komentářů.

    RENAME

    Příkaz sloužící k přejmenování objektů (např. tabulek, funkcí atd…).
• DML = „data manipulation language“
  • Tyto příkazy manipulují a upravují data v databázi. Jedná se o příkazy:

    INSERT

    Příkaz pro vložení dat do tabulky. Lze vložit jak jeden, tak i více řádků.

    INSERT INTO [tabulka] ([sloupce]) VALUES ([hodnoty])

    UPDATE

    Tímto příkazem můžeme upravovat již existující data v tabulce. Často se používá s podpříkazem WHERE, který omezuje úpravu jen na určité záznamy (bez něj by došlo k úpravě všech dat).

    UPDATE [tabulka] SET [sloupec] = [hodnota] WHERE [podmínka]

    DELETE

    Příkaz sloužící k odstranění záznamů v tabulce.

    DELETE FROM [tabulka] WHERE [podmínka]

    LOCK

    Příkaz používající se k zamčení tabulky. K tomu zamčení dochází při transakčním přístupu či jejich úpravě. Zamezí se tím to, že se data budou zároveň číst a zároveň upravovat.

    CALL

    Tento příkaz používáme k volání (spuštění) již vytvořených procedur a funkcí.

Omezení

V databází se omezení (angl. constraints) používají velice často. Jedná se o pravidla pro ukládání a manipulace s daty. Mohou se specifikovat jak při vytváření tabulky, tak také následně její úpravou. Rozdělují se na dva typy – Hodnotové a celotabulkové

Mezi používané omezení patří:

NOT NULL – určuje, že hodnota záznamu nesmí být NULL.

UNIQUE – určuje, že v celém sloupci musí být tato hodnota jedinečná.

PRIMARY KEY – přidává jak omezení NOT NULL, tak UNIQUE. Identifikuje tak řádek.

FORIEGN KEY – předchází zničení propojenosti tabulek.

CHECK – u toho omezí definujeme podmínku, kterou tato hodnota musí splňovat (např že je číslo větší jak 4).

DEFAULT – určuje výchozí hodnotu v případě, že není zadána.

Sekvence

Sekvence v databázových systémech jsou reprezentovány především po sobě jdoucími čísly, tím se tak generují unikátní čísla, která mohou reprezentovat daný záznam (řádek). V MySQL se pro tyto účely používá AUTO_INCREMENT. Jako odbočku lze zde ještě zmínit PostgreSQL, který k tomu používá samotný datový typ seriál. Zpět do MySQL, zde se AUTO_INCREMET používá především ve spojení s primárním klíčem, ve kterém je právě potřeba unikátních hodnot.

V MySQL je potřebné, aby sloupec, který má AUTO_INCREMENT, měl také omezení NOT NULL. V případě, že uživatel toto omezení nenastaví, nastaví si ho sám databázový systém. Je zde také omezení max 1 sekvence na tabulku.

Fungování sekvence v MySQL

Sekvence začíná na čísle 1 a poté je automaticky zvětšována o 1. Databázový systém si ukládá hodnotu, na které právě tato sekvence je. V případě odstranění řádku s vygenerovanou hodnotou systém toto odstranění neřeší (nevrací se zpět). Existuje také funkce LAST_INSERT_ID(), která vrátí poslední vygenerované číslo. Mohou tímto vzniknout také chyby, například pokud sekvence naposledy vygenerovala číslo 4, my následně vložíme řádek, kde ručně definujeme Id jako 5 a poté budeme chtít vložit další řádek bez definovaného Id (necháme to na AUTO_INCREMENT). Systém může zahlásit chybu, jelikož další vygenerované číslo bylo 5, záznam s tímto Id ale již existuje.

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10 MINVALUE=-100 MAXVALUE=1000;

CREATE SEQUENCE sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ]
[ CACHE [=] cache | NOCACHE ]
[ CYCLE | NOCYCLE]

Indexy

Indexy jsou struktury používané k rychlému vyhledávání v databázi. Vytváření se primárně na sloupcích, které se často používají právě k vyhledávání nebo na sloupcích, které jsou určeny jako primární nebo cizí klíč. Nejčastěji se pro indexy používají tvz. B-stromy, které jsou postavé speciálně pro tyto operace.

Indexy mohou vznikat vícero způsoby. Může je vytvořit sám uživatel, mohou vzniknout díky vytvoření klíče, ať už primárního, tak i cizího, a také mohou vzniknout při nastavení omezení UNIQUE. Tím se právě dostáváme k typům indexů.

Uikátní index – zajišťuje, že dva řádky nebudou mít stejnou hodnotu.

Primární klíč – jednoznačně identifikuje řádek v tabulce, jedná se tedy o druh unikátního indexu.

Cizí klíč – indexy odkazující na jiné primární klíče, slouží k zachování propojení mezi tabulkami.

Fulltext index – tento index je optimalizovaný pro vyhledávání v dlouhých textech (články, atd…)

Prefix index – zde se indexuje jen určitý počet znaků na začátku.

Spojené indexy – vznikají spojením indexů z více sloupců.

Uživatel může vytvořit vlastní index díky příkazu CREATE INDEX [name] on [table](column)

CREATE INDEX index_name ON table_name(column_name);

CREATE UNIQUE INDEX index_name ON table_name(column_name);

CREATE FULLTEXT INDEX index_name ON table_name(column_name);

CRUD operace

CRUD je zkratkou pro:

• C - CREATE
• R - READ
• U - UPDATE
• D – DELETE

Tato „zkratka“ určuje nejdůležitější operace, které by měl splňovat každý databázový systém). V MySQL jsou tyto příkazy INSERT (C), SELECT (R), UPDATE (U), DELETE (D).

Data dictionary

Data dictionary je slovník odkazující na soubory s metadaty. Tyto metadata slouží v databázovém systému pro uchování informací o jeho objektech, jako jsou tabulky, sloupce, indexy, práva, a další důležitá data. Využívají se pro efektivní práci databázového systému a pro rychlý přístup k datům. V MySQL jsou tyto data uložená separátně od těch uživatelských, ale lze pomocí systémových tabulek a pohledů do nich nahlížet a zjišťovat tak podrobnosti např o nastavení databázového systému nebo jeho uživatelích. Příkladově můžeme uvést virtuální databázi „INFORMATION_SCHEMA“ s informacemi o systému nebo schéma „mysql“, kde se uchovávají informace o uživatelích, oprávněních a přístupech.